﻿namespace DbWorker.AccessModels
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SQLite;
    using DbWorker.DataModels;
    using NLog;

    /// <summary>
    /// Access model to the file_metadata table.
    /// </summary>
    public class FileAm
    {
        private readonly SQLiteConnection _connection;
        private readonly Logger _logger = LogManager.GetCurrentClassLogger();

        public FileAm(SQLiteConnection connection)
        {
            _connection = connection;
        }

        /// <summary>
        /// Gets information about all files.
        /// </summary>
        public bool SelectAll(out List<FileDm> fileDms)
        {
            fileDms = new List<FileDm>();
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("SELECT * FROM file_metadata;", _connection);
                SQLiteDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    fileDms.Add(new FileDm
                    {
                        Id = (long)reader["id"],
                        LetterId = (long)reader["letter_id"],
                        FileName = (string)reader["file_name"],
                        Extention = (string)reader["extention"],
                        Hash = (string)reader["hash"],
                        Size = (long)reader["size"]
                    });
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);

                return false;
            }
        }

        /// <summary>
        /// Gets information about files attached to the letter.
        /// </summary>
        public bool SelectByLetter(long letterId, out List<FileDm> fileDms)
        {
            fileDms = new List<FileDm>();
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("SELECT * FROM file_metadata WHERE letter_id = @letter_id;", _connection);
                command.Parameters.AddWithValue("@letter_id", letterId);

                SQLiteDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    fileDms.Add(new FileDm
                    {
                        Id = (long)reader["id"],
                        LetterId = letterId,
                        FileName = (string)reader["file_name"],
                        Extention = (string)reader["extention"],
                        Hash = (string)reader["hash"],
                        Size = (long)reader["size"]
                    });
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);

                return false;
            }
        }

        /// <summary>
        /// Adds a new file information to the database.
        /// </summary>
        public bool Insert(FileDm fileDm, out long fileId)
        {
            fileId = -1;
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("INSERT INTO file_metadata "
                    + "(letter_id, file_name, extention, hash, size) VALUES "
                    + "(@letter_id, @file_name, @extention, @hash, @size); "
                    + "SELECT last_insert_rowid();", _connection);

                using (command)
                {
                    command.Parameters.AddWithValue("@letter_id", fileDm.LetterId);
                    command.Parameters.AddWithValue("@file_name", fileDm.FileName);
                    command.Parameters.AddWithValue("@extention", fileDm.Extention);
                    command.Parameters.AddWithValue("@hash", fileDm.Hash);
                    command.Parameters.AddWithValue("@size", fileDm.Size);

                    fileId = (long)command.ExecuteScalar();
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);

                return false;
            }
        }

        /// <summary>
        /// Changes the file information in the database.
        /// </summary>
        public bool Update(FileDm fileDm)
        {
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("UPDATE file_metadata SET "
                    + "letter_id = @letter_id, file_name = @file_name, "
                    + "extention = @extention, hash = @hash, size = @size "
                    + "WHERE id = @id;", _connection);

                using (command)
                {
                    command.Parameters.AddWithValue("@id", fileDm.Id);
                    command.Parameters.AddWithValue("@letter_id", fileDm.LetterId);
                    command.Parameters.AddWithValue("@file_name", fileDm.FileName);
                    command.Parameters.AddWithValue("@extention", fileDm.Extention);
                    command.Parameters.AddWithValue("@hash", fileDm.Hash);
                    command.Parameters.AddWithValue("@size", fileDm.Size);

                    command.ExecuteNonQuery();
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);

                return false;
            }
        }

        /// <summary>
        /// Deletes file information from the database.
        /// </summary>
        public bool Delete(long fileId)
        {
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("DELETE FROM file_metadata WHERE id = @id;", _connection);
                using (command)
                {
                    command.Parameters.AddWithValue("@id", fileId);
                    command.ExecuteNonQuery();
                }
                
                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);

                return false;
            }
        }

        /// <summary>
        /// Deletes files by letter id.
        /// </summary>
        public bool DeleteByLetter(long letterId)
        {
            try
            {
                if (_connection.State != ConnectionState.Open)
                    return false;

                var command = new SQLiteCommand("DELETE FROM file_metadata WHERE letter_id = @letter_id;", _connection);
                using (command)
                {
                    command.Parameters.AddWithValue("@letter_id", letterId);
                    command.ExecuteNonQuery();
                }

                return true;
            }
            catch (Exception ex)
            {
                _logger.Error(" -- {0}.{1} -- {2}", GetType().FullName,
                    System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message);

                return false;
            }
        }
    }
}
